- Special Edition Using Visual Basic Script -

CHAPTER 23 - Microsoft SQL Server 6.5 Web Extensions

by Ibrahim Malluf

Microsoft has embraced the Internet with enthusiasm, as witnessed by the explosion of new products directly targeted at the Internet and by the revamping of existing products to accommodate the Internet. SQL Server is no exception to this drive by Microsoft to place its product line in the forefront of Internet development. Besides the MS Internet Information Server's IDC, which allows for interactive data operations, Microsoft has added a set of extensions to SQL-Server itself that provide a means for easily publishing your data on the Internet. They are called the SQL Server 6.5 Web Extensions.

In this chapter, you learn:

• About the new Web extensions to SQL Server 6.5
• These extensions come in the form of three stored procedures; sp_makewebvtask, sp_dropwedtask, and sp_runwebtask.
• About using the Web Assistant, a new wizard supplied with SQL Server 6.6
• The wizard enables you to create static web pages from your SQL Server databases.
• How to create custom template files for the SQL Server 6.5 Web extensions
• These extensions include VBScript as part of those templates.

Introducing the SQL Server 6.5 Web Extensions

The core of this Web publishing capability is the addition of three new stored procedures; sp_dropwebtask, sp_makewebtask, and sp_runwebtask. By using these three new stored procedures you can generate HTML Web pages. You can generate these pages on demand, at regular timed intervals, or from triggers designed to react to the changes of critical data. There is even a Web Assistant tool to help you create these pages, which I will discuss later in the chapter. For now let's go over each of these new procedures.

Creating Web Tasks by Using sp_makewebtask

The sp_makewebtask creates a task in SQL Server that makes HTML pages from a set of properties described in the procedures syntax. Listing 23.1 shows this syntax. I am expecting that most of you reading this chapter have a fair knowledge of SQL Server's stored procedure syntax; so I'll just go over the parts that are really HTML page specific. More detailed information is available using the SQL Server's Books Online or the What's New In SQL Server 6.5 manual. The parameter specifications I am showing here come from there. I added additional comments and modifications when I found things to be a little more flexible than described in the original documentation.

Listing 23.1 The sp_makewebtask Syntax

The following list explains the parameters in more detail:

• @outputfile is the path where the generated HTML file should be stored. This can be a UNC name if the file is to be created on a remote computer. This parameter is required and must be unique for each task created with sp_makewebtask. The outputfile variable is of varchar datatype and has a maximum of 255 characters.
• @query specifies the query to be run and is a required parameter. The results of the query will be displayed in the HTML document in a table format when the task is run with sp_runwebtask unless your template file uses a different scheme. Multiple SELECT queries can be specified and will result in multiple rowsets returned to the @outputfile. The @query parameter is of text datatype.
• @colheaders determines whether the query results are displayed with column headers (1) or no column headers (0). Column headers (1) is the default. The colheaders variable is of tinyint datatype.
• @lastupdated determines whether the generated HTML document displays a Last Updated: timestamp indicating the last updated date and time (1) or no timestamp (0). Timestamp (1) is the default. The timestamp appears one line before the query results in the HTML document. The lastupdated variable is of tinyint datatype.
• @HTMLHeader specifies the HTML formatting code for displaying the text contained in the @resultstitle variable. The HTMLHeader variable is of tinyint datatype. These are the values.

• @templatefile is an optional parameter specifies the path of the template file used to generate the HTML document. This is also where the VBScript programmer can really provide powerful adaptions to the standard output usually generated by SQL Server 6.5. While I am including the description of output here that Microsoft uses, I show you later in the chapter how to bypass the need to display the row data in tables and instead use that data in other ways. The template file contains information about formatting characteristics for HTML documents and contains the tag <%insert_data_here%>, which indicates the position to add the query results in an HTML table. The templatefile variable is of varchar datatype and has a maximum of 255 characters If @templatefile is specified, the following parameters are ignored when the HTML file is generated; @bold, @singlerow, @fixedfont, @tabborder, @HTMLHeader, @table_url, @italic, @URL, @lastupdated, @url_quer, @reftext, @webpagetitl, and @resultstitle. There are two ways to specify the location of the results of a query in a template file:
• Include the tag <%insert_data_here%>, which indicates the position to add the query results in an HTML table. There are no spaces between the less than sign (<) and the "%i" of %insert and between the "e%" of here% and the greater than sign (>).
• Specify a complete row format between the keywords <%begindetail%> and <%enddetail%> including <TR>, </TR>, <TD>, and </TD> HTML tags. For each column to display in the results set, include the tag <%insert_data_here%> inside the row format keywords. Specifying the complete row format produces a more precise layout of the results set. When used with this complete row format, @colheaders is ignored.
• @webpagetitle is the title of the HTML document which is place between the <TITLE>...</TITLE> tags. The default is SQL Server Web Assistant. The webpagetitle variable is of varchar datatype and has a maximum of 255 characters.
• @resultstitle specifies the title displayed above the query results in the HTML document. The default title is Query Results. The resultstitle variable is of varchar datatype and has a maximum of 255 characters.
• @URL provides a hyperlink to another HTML document. The hyperlink is placed after the query results and at the end of the HTML document. This parameter is used to provide links from your document to other documents. If this parameter is specified, @reftext must also be specified, and @table_urls and @url_query cannot be specified. The URL variable is of varchar datatype and has a maximum of 255 characters. If @table_urls = 1, then @url_query must be included to specify the query to be executed for retrieving hyperlink information, and @URL and @reftext cannot be specified. Information is specified either in @URL and @reftext, or in @url_query and @table_urls.
• @reftext is the the hyperlink text that describes to what HTML document the hyperlink should take the user. The hyperlink text describes the destination and the hyperlink address comes from the URL in the @URL parameter. The reftext variable is of varchar datatype and has a maximum of 255 characters.
• @table_urls determines whether hyperlinks are be included on the HTML document and whether the hyperlinks will come from a SELECT statement executed on Microsoft SQL Server. A value of 0 (the default) indicates that there is no query that will generate hyperlinks for the HTML. @URL and @reftext may still be specified for a single hyperlink. A value of 1 indicates that a list of hyperlinks will be created by using @url_query. The table_urls variable is of tinyint datatype. If @table_urls = 1, then @url_query must be included to specify the query to be executed for retrieving hyperlink information, and @URL and @reftext cannot be specified. Information is specified either in @URL and @reftext, or in @url_query and @table_urls.
• @url_query is the SELECT statement used to create the URL and its hyperlink text. URLs and hyperlink text come from a SQL Server table. With this parameter, it is possible to generate multiple URLs with associated hyperlinks. Use @url_query with @table_urls. The url_query variable is of varchar datatype with a maximum of 255 characters. The url_query variable must return a results set containing two columns: The first column is the address of a hyperlink and the second column describes the hyperlink. The number of hyperlinks inserted into the HTML document equals the number of rows returned by executing @url_query.
• @whentypen is a required parameter that specifies when to run the task that creates the HTML document. The whentype variable is of tinyint datatype. The possible values are described in Table 23.1.

Table 23.1 The Values and Their Meanings of the @whentypen Parameter

While not a comprehensive description of the sp_makewebtask, it is enough to get us started with the examples that will be provided later. I would like to make note of the fact that a whentype() other than 1 will result in a task being stored as a repeatable task within your database that will be called according to the specified @whentyen. To remove this task you will need to use the sp_dropwebtask stored procedure or directly delete it from the tasks list.

Using the sp_dropwebtask to Remove Webtasks

The sp_dropwebtask is a much simpler stored procedure than the preceding one. Its purpose is simpler as well. It merely deletes a previously defined Web task that was created by the sp_makewebtask stored procedure. The syntax is shown in Listing 23.2.

sp_dropwebtask {@procname = procname | @outputfile = outputfile

[ccc]| @procname = procname, @outputfile = outputfile}

Using sp_runwebtask to Specifically Run a Previously Created Web Task

sp_runwebtask {@procname = procname | @outputfile = outputfile |

Using the SQL Server Web Assistantùfor the Faint of Heart

Does the prospect of directly using the sp_makewebtask stored procedure intimidate you? Relax! The Web Assistant is a wizard-type program that provides a step-by-step interface for creating standard HTML pages from your database. The Web Assistant uses Transact-SQL queries, stored procedures, and extended stored procedures as the foundation for the generated HTML pages. You can let the Web Assistant design an HTML page that includes a table containing all the data in standard table rows for you. The alternative is to provide a template file that contains a custom format for the creation of your HTML pages. Figure 23.1 shows the opening screen of the Web Assistant. In order to use the Web Assistant, you need to have sufficient privileges to create procedures, files, and select privileges on columns. To run Web Assistant double-click the Web Assistant icon in the SQL Server program group.

FIG. 23.1

The opening screen for the Web Assistant.

When starting the Web Assistant, specify the server that contains the database and provide either a log on id, and password, or alternatively select the log on security of NT itself. In Figure 23.1 you can see that I chose the latter. Like all other Wizards, you select the next button to proceed on to the next step. The Web assistant logs on to the server and presents you with a window that gives you a choice of three different ways to construct your HTML pages.

Interactively Creating a Query

To interactively create a query from which your HTML page will be built, follow these steps:

1. From the query screen, select "Build a Query" from a Database Hierarchy option.

2. From the database treeview at the bottom of the window, select and expand your database to get to the tables and columns you want to include. To expand a database object, click the plus sign (+) next to it.

3. From the expanded database list, select the table(s) to use in your query. This option is equivalent to a SELECT * FROM table_name statement.

4. To select individual columns, expand the table selection. All columns are selected by default. To cancel a selection, clear the column(s).

5. In the Do You Want Any Additional Criteria... box, type additional clauses for the query, such as WHERE or ORDER BY.

6. To continue, choose the Next button. To exit, choose the Cancel button.

While this method works and is real easy to use for those who are novices with Transact-SQL, I found it to be an awkward and limiting interface for those who are competent in writing their own SQL queries. The Build a Query From a Database Hierarchy window is shown in Figure 23.2.

FIG. 23.2

Using the Web Assistant's Build A Query From a Database Hierarchy option.

Entering a Query as Freeform Text Option

With this option, there are no training wheels, you have to create your query as you see fit and make sure that it will act as you desire. Of course, with this option you can create a rowset that can be as complicated as your needs require, without restraints. In the example I am going to use here, I developed and tested the queries in ISQL/w, a utility program that comes with SQL-Server (see fig. 23.3).

FIG. 23.3

Using ISQL/w to create queries.

Looking at the Query window in Figure 23.3, you can see that I have two Select type queries in the window. The Web Assistant treats these as two table sets in the resulting HTML page. All I need to do is cut and paste the perfected query into the Web Assistant's Freeform Query text window, as shown in Figure 23.4. Listing 23.2 shows the query that I developed for this chapter.

Listing 23.2 Muti Select Query that Results in Two Rowsets Being Returned

FIG. 23.4

The perfected Transact-Sql Query pasted from ISQL/w into the Web Assistant.

Scheduling a Web Page Task

With the query now pasted into the Web Assistant, I move on to the next window, which includes a single drop down box titled Scheduling Options. There are five choices that determine the life span of your Web page task they are described in the following list:

• NOW Creates the HTML page immediately and deletes the task from the database when finished. This is a one-time task.
• LATER Creates the HTML page at a later date & time that you specify in an argument window. This is also a one time task that is deleted immediately after it runs on the day and time specified.
• WHEN DATA CHANGES Creates triggers based on your selection of tables, and/or columns to monitor. This task remains until removed and repeats every time that one of the triggers is called.
• ON CERTAIN DAY OF THE WEEK The task executes on the specified days of the week until the task is dropped.
• ON A REGULAR BASIS The task repeats on a regular schedule of n minutes, hours, days, or weeks. The task repeats until dropped from the task list.

This is a very flexible system for scheduling Web page updates. You can schedule one time tasks, triggered tasks, or regular timed schedules. Figure 23.5 shows the Web Assistant indow in the When Data changes Mode. The format of this window changes depending on your selection of a scheduling option.

FIG. 23.5

Setting a scheduling option in the Web Assistant.

Using File Options with the Web Assistant

The next Web Assistant page, after scheduling, is the Files Options page. Of course the first option is the final location path of the HTML page to be generated. You need to provide a fully qualified path, or a UNC path for the page to which you are writing. In the next option, you decide if you want to use a template file. I will go into more detail on this option in the "Building a VBScript Based Web Page" later in this chapter. For now I'll leave it unchecked and the Web Assistant will progress on to some basic page customizations. On the File Options page, there is also an option for including one URL link, or several links from a table in the database. Using this option, places one or more URL links at the bottom of the resulting Web page. You can view the File Options page in Figure 23.6.

FIG. 23.6

Filling in the File Options of the Web Assistant.

Formatting the Page with the Web Assistant

Don't get exited; there are not a lot of options here. You can select a header type for the page title from a drop down list that includes H1 to H6. You can also select fixed or proportional font, bold and/or italic text for the output data. You can also add a Date-time stamp to the top of the page and allow column names for the data tables. Figure 23.7 shows the resulting HTML page from the supplied query.

FIG. 23.7

The HTML page made by the Web Assistant shown in Explorer 3.0b1.

Building a VBScript-Based Web Page

If the Web page shown in Figure 23.7 knocked your socks off and met with your ideas of what a Web page should look like, move on to the next chapter because you are done here.

Still reading? Okay, let's build a real Web page using the Web Assistant. There are only two things you have to do: write a template file, and specify that file in the File Options page. In this example, I want to have a ComboBox loaded with publisher names. When I select a publisher from the ComboBox, I want a book printed by that publisher to appear in the text boxes below. The first thing we have to do is create the query that drives the page. That query is in Listing 23.2 shown earlier in the chapter. The next step is to create a template that the Web Assistant will use to make this page. The first part of the template's code populates the template with the ActixeX controls that will contain the data and lay out the visible part of the HTML page. This part is contained in Listing 24.3.

Listing 23.1ùQUEWEB2.TPL The Layout Portion of the Template File Including the ActiveX Control Declarations

The ComboBox and ListBox need to be populated with the rowsets returned from the query. I should note here that a ListBox is being used to receive data in place of a data array. At the time of writing this book, arrays were not yet working in VBScript. By the time you read this, they should be working, which will make some of the contortions being performed here unnecessary. Anyway, what I do is load all of the query results into these two controls separating the fields with a semicolon. Use the <%insert_data_here%> place holder to mark where the data fields should be placed. The fields will be filled in the order that the query requests them in the Select part of the query. Take a look at that code now in Listing 23.4.

Listing 23.4ùQUEWEB2.TPL The Code that Inserts the Query's Data into the ActiveX Controls

The <%begindetail%><%enddetail%> pair mark off the boundaries of a given rowset. That means that the procedure will iterate through all of the rows returned and place the data in that rowset repeatedly, along with what ever other code is between those markers, in the resulting file. To understand what I mean, look at the corresponding section in the resulting HTML page in Listing 23.5.

Listing 23.5ùQUEWEB2.HTML The Resulting Section of Code Returned by the sp_makeweb Procedure

What you see in Listing 24.5 is the results of the first <%begindetail%><%enddetail%> pair. Finally to complete our template file, add the VBScript code that parses the data into its component parts and loads those parts into the TextBoxes. There is also a click event of the ieCombo that calls the parsing procedure. It's all in Listing 24.6.

Listing 23.6ùQUEWEB2.TPL The click Event and Parsing Procedures

After this template file is stored include the template file in the Web Assistant's Option page by specifying the fully qualified path to it. After completing the remaining steps of the Web Assistant's process, the page will be created by SQL Server. That page will look like the one in Figure 23.8.

FIG. 23.8

The queweb2.html page displayed in Explorer 3.0b1.

The above example shows how the Web Assistant can help you to create your Web pages. As another example, using only the sp_makewebtask procedure, I'll show how you can construct procedures that will do the same thing without the Web Assistant. First I'll make a slightly prettier template file that uses a table to contain the detail TextBoxes. The code in Listing 23.7 shows that template file.

Listing 23.7ùSQL6WEB.TPL A Template File Using a Table and Text Box Combination to Display Data

To use this template in conjunction with the sp_makewebtask stored procedure we'll call up the ISQL/w file. And enter the text shown in Listing 23.10. The ISQL/w query screen looks like Figure 24.9.

FIG. 23.9

Using the ISQL/w program to execute the sp_makewebtask procedure.

Listing 24.2ùWEBTEST4.SQL A Typical sp_makewebtask Procedure

Just by executing this query the web page shown in Figure 23.10 will be created.

FIG. 23.10

The Web page created by the query in Listing 23.8.

From Here...

The Web extensions for SQL Server provide a very attractive alternative to the more complex IDC method of providing data. The stress on the server is also minimized since the pages are not created during user demand time. The Web server merely sends the already existing pages out. The examples shown here are really simple and do not touch on too deeply on more complex strategies that the Web Extensions could be targeted towards. What was presented here was really intended to show you how you could use VBScript to enhance the Web pagers produced through the use of customized template files.